<?php
require_once '../lib/Validate.php';
require_once 'model.php';
/**
 * Offer_model Class
 * Model for the Offers table
 * 
 * @author Eduardo Nava
 *
 */
class offer_model extends model{
	/**
	 * Constructor
	 * @return void
	 */
	function __construct(){
		parent::__construct('offers');
	}
	
	
	/**
	 * Tests
	 */
	function getAll($pageNum = 1, $pageSize = 10){

		$offers = array(1=>array('id'=>'1','price'=>'10','venue_id'=>'1','venue_name'=>'BarBQ','rounded_rating'=>'3',
		'beers'=>array(1=>array('id'=>'1','brewery_name'=>'Polar','name'=>'light'),
		2=>array('beer_id'=>'2','beer_brand'=>'Polar','beer_name'=>'ice')),
		'date_from'=>'Today','date_to'=>'tomorrow'),
		2=>array('id'=>'2','price'=>'12','venue_id'=>'2','venue_name'=>'BarCode','rounded_rating'=>'2',
		'beers'=>array(1=>array('id'=>'3','brewery_name'=>'Cascade','name'=>'light')),
		'date_from'=>'Today','date_to'=>'tomorrow')
		);
		return $offers;
	}

	/**
	 * MAin Offer Search Function
	 * @param $args The parameters to be filtered (Suburb,beer_name,price, etc)
	 * @return n ssociative Array with found Offer's info
	 */
	function find($args){
//		$sql="SELECT offers.id, price, description, date_from, date_to, time_from, time_to, repeats, 
//		beers.id as beer_id,beers.name AS beer_name, type, breweries.name AS brewery_name, country, picture, 
//		users.name AS user_name, venues.id as venue_id,venues.name AS venue_name, venues.suburb as suburb, website, venues.email, 
//		rating.exact_rating, rating.rounded_rating FROM beers_offers, beers, breweries, offers, 
//		users, venues, (SELECT venue_id, ROUND(AVG(rate_level_id)) AS rounded_rating,
//		 AVG(rate_level_id) AS exact_rating FROM venue_rates GROUP BY venue_id) AS rating WHERE 
//		 beers_offers.offer_id = offers.id AND breweries.id = beers.brewery_id AND beers_offers.beer_id = 
//		 beers.id AND users.id = offers.user_id AND venues.id = offers.venue_id AND venues.id = 
//		 rating.venue_id";
		
//		$sql="SELECT offers.id AS id, description, price, size, size.id AS size_id, date_from, 
//		date_to, time_from, time_to, repeats, venues.name AS venue_name, suburbs.name AS suburb_name, 
//		beers.name AS beer_name, beers.id AS beer_id, users.name AS user_name, IFNULL(rounded_venue_rate, 0) as rounded_venue_rate, 
//		IFNULL(exact_venue_rate, 0) FROM suburbs, beers, venues, offers, beers_offers, users, size, 
//		(SELECT venue_id, ROUND(AVG(rate_level_id)) AS rounded_venue_rate, AVG(rate_level_id) AS 
//		exact_venue_rate FROM venue_rates GROUP BY venue_id) AS ratings_for_venue 
//		WHERE beers.id = beers_offers.beer_id AND users.id = offers.user_id AND offers.venue_id = venues.id  
//		AND venues.id = ratings_for_venue.venue_id AND size.id  = offers.size_id AND 
//		suburbs.id  = venues.suburbs_id ";
//		
		$sql = "SELECT offers.id AS offer_id, description, price, size, size.id AS size_id, date_from, 
		date_to, time_from, time_to, repeats, offers.timestamp AS offer_timestamp,venues.street as street,venues.id as venue_id, venues.name AS venue_name,
		suburbs.name AS suburb_name, users.name AS user_name, IFNULL(rounded_venue_rate,0) AS 
		rounded_venue_rate, IFNULL(exact_venue_rate,0) AS exact_venue_rate, beers_offers.beer_id AS beer_id,
		beers.name AS beer_name FROM beers, beers_offers, suburbs, venues, offers, users, size, 
		(SELECT offers.venue_id, ROUND(AVG(rate_level_id)) AS rounded_venue_rate, AVG(rate_level_id) 
		AS exact_venue_rate FROM offers LEFT JOIN venue_rates ON (offers.venue_id = venue_rates.venue_id)
		GROUP BY venue_id) AS ratings_for_venue WHERE users.id = offers.user_id AND offers.venue_id = 
		venues.id AND venues.id = ratings_for_venue.venue_id AND size.id = offers.size_id AND suburbs.id = 
		venues.suburbs_id AND beers_offers.offer_id = offers.id AND beers.id = beers_offers.beer_id";
	

		
		$suburb = "";
		if (!empty($args['suburb'])){
		$suburb = ($args['suburb']!='' ? " and suburbs.name LIKE '%".$args['suburb']."%' ":"");
		}
		$price = "";
		if (!empty($args['price'])){
		$price = ($args['price']!='' ? " and price <=".$args['price']:"");
		}
		$venue_id = "";
		if (!empty($args['venue_id'])){
		$venue_id = (!empty($args['venue_id']) ? "and venues.id =$args[venue_id] ":'');
		}
		
		$beer_name="";
		if (!empty($args['beer_name'])){
		$beer_name = ($args['beer_name']!=''? " AND beers.name LIKE '%".$args['beer_name']."%' " :"");
		}
		
		$beer_id="";
		if (!empty($args['beer_id'])){
		$beer_id = ($args['beer_id']!=''? " AND beers.id = ".$args['beer_id']:"");
		}
		
		$sql2="";
	if ($beer_name!="" ||$beer_id!=""){	
	$sql2=" AND offers.id IN (SELECT q1.offer_id FROM 
	(SELECT offers.id AS offer_id, description, price, size, size.id AS size_id, 
	date_from, date_to, time_from, time_to, repeats, offers.timestamp AS offer_timestamp, 
	venues.name AS venue_name, suburbs.name AS suburb_name, users.name AS user_name, IFNULL
	(rounded_venue_rate,0) AS rounded_venue_rate, IFNULL(exact_venue_rate,0) AS exact_venue_rate, 
	beers_offers.beer_id AS beer_id FROM beers, beers_offers, suburbs, venues, offers, users, size, 
	(SELECT offers.venue_id, ROUND(AVG(rate_level_id)) AS rounded_venue_rate, AVG(rate_level_id) AS 
	exact_venue_rate FROM offers LEFT JOIN venue_rates ON (offers.venue_id = venue_rates.venue_id) GROUP BY 
	venue_id) AS ratings_for_venue WHERE users.id = offers.user_id AND offers.venue_id = venues.id AND 
	venues.id = ratings_for_venue.venue_id AND size.id = offers.size_id AND suburbs.id = venues.suburbs_id 
	AND beers_offers.offer_id = offers.id AND beers.id = beers_offers.beer_id $beer_id $beer_name
	) AS q1 JOIN
	(SELECT beers.name AS beer_name, beers.id AS beer_id, beers_offers.offer_id AS 
	offer_id FROM beers_offers, beers WHERE beers.id = beers_offers.beer_id) AS q2 ON 
	(q1.offer_id = q2.offer_id))";
			$sql2=" AND offers.id IN (
SELECT q1.offer_id FROM 
(
SELECT offers.id AS offer_id, description, price, size, size.id AS size_id, date_from, date_to, 
time_from, time_to, repeats, offers.timestamp AS offer_timestamp, venues.name AS venue_name, 
suburbs.name AS suburb_name, users.name AS user_name, IFNULL(rounded_venue_rate,0) AS rounded_venue_rate, 
IFNULL(exact_venue_rate,0) AS exact_venue_rate, beers_offers.beer_id AS beer_id FROM beers, beers_offers,
 suburbs, venues, offers, users, size, (SELECT offers.venue_id, ROUND(AVG(rate_level_id)) AS 
 rounded_venue_rate, AVG(rate_level_id) AS exact_venue_rate FROM offers LEFT JOIN venue_rates ON 
 (offers.venue_id = venue_rates.venue_id) GROUP BY venue_id) AS ratings_for_venue WHERE users.id = 
 offers.user_id AND offers.venue_id = venues.id AND venues.id = ratings_for_venue.venue_id AND size.id = 
 offers.size_id AND suburbs.id = venues.suburbs_id AND beers_offers.offer_id = offers.id AND beers.id = 
 beers_offers.beer_id  $beer_name $beer_id
) AS q1 JOIN
(SELECT beers.name AS beer_name, beers.id AS beer_id, beers_offers.offer_id AS offer_id FROM beers_offers, beers WHERE beers.id = beers_offers.beer_id) AS q2 ON (q1.offer_id = q2.offer_id))";
	
	}

		if ($suburb!='' || $price!=''|| $venue_id!=''){
		$sql= "$sql $suburb $price $venue_id ";
		}
		
		$sql = $sql . $sql2 . " group by offer_id ";
		
		$sort = (!isset($args['sort']) ? " order by price" : " order by ".$args['sort']);
		$page = (!isset($args['page']) ? '0':$args['page']);
		
		
		$recordStart = (int)$page * $this->pageSize;
		$limit = " LIMIT $recordStart, $this->pageSize";
		$sql = "$sql $sort $limit";
//			echo "$sql <br>"; exit();
			try{
			//$stmt = $this->dbCon->prepare($sql);
			//$params = array(':id'=>$id);
			$stmt=$this->dbCon->query($sql);
			//$stmt->execute($sql);
			$records= $stmt->fetchAll(PDO::FETCH_ASSOC);
			$procRecords =array();

		$beer = new model("beers");
		foreach($records as $record){
				$beers = array();
				$offer_beers=$this->get_beers($record['offer_id']);
				foreach ($offer_beers as $offerID_beerID) {
					$beers[] = $beer->get($offerID_beerID['beer_id'])	;
				}
				//$record['beers']=array($record['beer_id']=>array('id'=>$record['beer_id'],'name'=>$record['beer_name']));
				$record['beers']=$beers;
				$procRecords[$record['offer_id']]=$record;

		}
		
		return $procRecords;
		}catch (PDOException $e){
			//TODO find proper way to display message?
			echo $e;
			return null;
		}
	}

	
	
/**
 * Retrieves beers related to this offer
 * @param $offer_id The ID of the offer
 * @return an associative array with Beer Info
 */
	//TODO Use Appropriate Model (Beer_model)
function get_beers($offer_id){
		$sql = "SELECT * FROM beers_offers WHERE offer_id=$offer_id";
		try{
			return  $this->dbCon->query($sql)->fetchAll(PDO::FETCH_ASSOC);
			
		}catch (PDOException $e){
			//TODO find proper way to display message?
			echo $e;
			return null;
		}
	}
	
	/**
	 * Function to Create a New Offer
	 * 
	 */
	function create($args){
	try{
	$offer = new model("offers");
	$beersOffers = new model("beers_offers");
	$beersOffers->tableKey="offer_id";
	//$this->dbCon->beginTransaction();
	$record = $offer->create($args);
	if ($record==false){
		//$this->dbCon->rollBack();
		return false;
	}
	$offer_id = $record[0]["id"];

	foreach ($args['beers'] as $beer) {
		$beers_offers=array("offer_id"=>$offer_id,"beer_id"=>$beer);
		$beersOffersRecord=$beersOffers->create($beers_offers);		
		if($beersOffersRecord==false){
			//$this->dbCon->rollBack();
			return false;
		}
	}
	
	//$this->dbCon->commit();
	return true;
	}catch(PDOException $e){
	//$this->dbCon->rollBack();
	}


	}
}
?>